import sqlite3


def create(conn):
    """
    创建相应的数据表
    """
    sql_create = '''
    CREATE TABLE `users` (
      `id`  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
      `username`  TEXT NOT NULL UNIQUE,
      `password`  TEXT NOT NULL,
      `email` TEXT
    )
    '''
    # 用 execute 执行一条 sql 语句
    conn.execute(sql_create)
    print('创建成功')


def insert(conn, username, password, email):
    """
    插入一行的数据
    """
    sql_insert = '''
    INSERT or ignore INTO
      users(username, password, email)
    VALUES
      (?, ?, ?);
    '''
    # 下面写法会被 SQL 注入
    # sql = '''
    # INSERT INTO
    #   users(username, password, email)
    # VALUES
    #   ("{}", "{}", "{}")
    # '''.format('123', '345', 'a.com')
    # conn.execute(sql)
    # 参数拼接要用 ?, execute 中的参数传递必须是一个 tuple 类型
    conn.execute(sql_insert, (username, password, email))
    print('插入数据成功')


def select(conn, usr, pwd):
    """
    根据不同的条件查找数据
    当用户名和密码都正确时, 查询数据
    """
    sql = '''
    SELECT
        id, username, email
    FROM
      users
    WHERE
      username=? and password=?
    '''
    cursor = conn.execute(sql, (usr, pwd))
    print('一条数据', list(cursor))

    # 被 SQL 注入的情况
    # usr = 'yin" or "1"="1'
    # pwd = 'gua'
    # sql = '''
    # SELECT
    #     id, username, email
    # FROM
    #     users
    # WHERE
    #     username="{}" and password="{}"
    # '''.format(usr, pwd)
    # cursor = conn.execute(sql)
    # print('一条数据', list(cursor))


def delete(conn, user_id):
    """
    根据 id 删除对应的那条数据
    """
    sql_delte = '''
    DELETE FROM
      users
    WHERE
      id=?
    '''
    # tuple 只有一个元素的时候必须是这种写法
    conn.execute(sql_delte, (user_id,))


def update(conn, user_id, email):
    """
    更新相应部分的数据
    """
    sql_update = '''
    UPDATE`users`
    SET
      `email`=?
    WHERE
      `id`=?
    '''
    conn.execute(sql_update, (email, user_id))


def main():
    # 指定数据库名字并打开, 无此数据库, 自动创建
    db_path = 'tb.sqlite'
    conn = sqlite3.connect(db_path)
    print("打开了数据库")

    # create(conn)
    # insert(conn, 'sql_', '123', 'a@qq.com')
    # delete(conn, 1)
    update(conn, 3, '119@qq.com')
    select(conn, 'asdads', 'asd')

    # 必须用 commit 函数提交你的修改
    # 否则你的修改不会被写入数据库
    conn.commit()
    conn.close()

if __name__ == '__main__':
    main()


